package com.sishu.njrtsdms.domain.core;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.StrUtil;
import com.sishu.njrtsdms.utils.PageUtils;
import org.jooq.Record;
import org.jooq.*;
import org.jooq.conf.Settings;
import org.jooq.impl.DAOImpl;
import org.jooq.impl.DSL;
import org.jooq.impl.SQLDataType;
import org.jooq.impl.TableImpl;
import org.jooq.types.ULong;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;

import java.util.*;

import static com.sishu.njrtsdms.jooq.codegen.Tables.*;
import static org.jooq.impl.DSL.*;


public abstract class ExtendDAOImpl<R extends UpdatableRecord<R>, P, T> extends DAOImpl<R, P, T>
        implements ExtendDAO<R, P, T> {


    /**
     * ID类型JSON数组数据转行查询
     */
    protected static final Select<Record1<ULong>> ID_JSON_ARRAY_TO_ROWS_SELECT =
            select(field("value", SQLDataType.BIGINTUNSIGNED))
                    .from(jsonTable(TBL_TEACH_CON_PRJ.PROJECT_LEADERS,
                            val("$[*]", SQLDataType.VARCHAR))
                            .column("value", SQLDataType.BIGINTUNSIGNED)
                            .path("$")
                            .as("value")
                    );


    /**
     * 字典数据表简略信息前置WITH
     */
    public static final CommonTableExpression<Record6<ULong, String, String, String, String, JSON>>
            dictDataSimpleWithExpression =
            name("dict_data")
                    .as(select
                            (
                                    TBL_DICT_DATA.DICT_DATA_ID,
                                    cast(TBL_DICT_DATA.ANCESTORS, SQLDataType.VARCHAR).as(TBL_DICT_DATA.ANCESTORS),
                                    TBL_DICT_DATA.DICT_DATA_LABEL,
                                    TBL_DICT_DATA.DICT_DATA_VALUE,
                                    TBL_DICT_DATA.DESC,
                                    jsonObject(
                                            jsonEntry("dictTypeId", TBL_DICT_TYPE.DICT_TYPE_ID),
                                            jsonEntry("ancestors", cast(TBL_DICT_TYPE.ANCESTORS, SQLDataType.VARCHAR)),
                                            jsonEntry("dictTypeName", TBL_DICT_TYPE.DICT_TYPE_NAME),
                                            jsonEntry("dictTypeCode", TBL_DICT_TYPE.DICT_TYPE_CODE),
                                            jsonEntry("desc", TBL_DICT_TYPE.DESC)
                                    ).as("dictTypeSimpleVOJSON")
                            )
                            .from(TBL_DICT_DATA)
                            .innerJoin(TBL_DICT_TYPE)
                            .on(TBL_DICT_DATA.DICT_TYPE_ID.eq(TBL_DICT_TYPE.DICT_TYPE_ID))
                    );


    protected ExtendDAOImpl(Table<R> table,
                            Class<P> type) {
        super(table, type);
    }

    protected ExtendDAOImpl(Table<R> table,
                            Class<P> type,
                            Configuration configuration) {
        super(table, type, configuration);
    }


    @Override
    public P fetchOne(Condition condition) {
        return ctx().selectFrom(getTable())
                .where(condition)
                .orderBy(getPrimaryKeyFieldList())
                .fetchOne(mapper());
    }

    @Override
    public Optional<P> fetchOneOptional(Condition condition) {
        return Optional.ofNullable(fetchOne(condition));
    }

    @Override
    public List<P> fetch(Condition condition,
                         SortField<?>... sortFields) {
        return ctx().selectFrom(getTable())
                .where(condition)
                .orderBy(sortFields)
                .fetch(mapper());
    }

    @Override
    public List<P> fetchById(Collection<T> ids) {
        return fetch(equalPrimary(ids), new SortField[]{});
    }

    @SuppressWarnings("unchecked")
    private Condition equalPrimary(Collection<T> ids) {
        Condition condition;
        var pkList = getPrimaryKeyFieldList();
        if (pkList.size() == 1) {
            var singlePrimaryKeyField = pkList.get(0);
            if (ids.size() == 1)
                condition = ((Field<Object>) singlePrimaryKeyField)
                        .eq(ids.iterator().next());
            else
                condition = singlePrimaryKeyField.in(ids);
        } else {
            condition = row(pkList).in(ArrayUtil.toArray((Collection<Record>) ids, Record.class));
        }
        return condition;
    }

    @Override
    public PageInfo<P> fetchPage(Pageable page) {
        return fetchPage(page, DSL.noCondition());
    }

    @Override
    public PageInfo<P> fetchPage(Pageable page,
                                 Condition condition,
                                 SortField<?>... sortFields) {
        return fetchPage(page, ctx().selectFrom(getTable())
                .where(condition)
                .orderBy(sortFields));
    }

    @Override
    public PageInfo<P> fetchPage(Pageable page,
                                 SelectLimitStep<?> selectLimitStep) {
        return fetchPage(page, selectLimitStep, r -> r.into(getType()));
    }

    @Override
    public <O> PageInfo<O> fetchPage(Pageable page,
                                     SelectLimitStep<?> selectLimitStep,
                                     RecordMapper<? super Record, O> mapper) {
        if (page == null || page.isUnpaged()) return PageInfo.emptyPageInfo();

//        var total = ctx().fetchCount(selectLimitStep);
//        int total = ctx()
//                .selectCount()
//                .from(selectLimitStep)
//                .fetchOptional(0, int.class)
//                .orElse(0);
//        if (total == 0) return PageInfo.empty();
        /*
            如果数据总数小于页面大小,则重新构造Pageable单页面对象
            否则会导致page#getOffset方法返回值不正确,导致最后响应的total 与实际不符
         */
        var pageSize = page.getPageSize();
        var pageNumber = page.getPageNumber();
//        if (total < pageSize) page = PageRequest.of(0, total);
        // limit 语句查询时的offset
        var limitClauseOffset = pageNumber > 0 ?
                (pageNumber - 1) * pageSize : 0;
        var contentList = selectLimitStep
                .limit(pageSize)
                .offset(limitClauseOffset)
                .fetch(mapper);

        return new PageInfo<>(contentList, pageNumber, pageSize);
    }


    @Override
    public <O> PageInfo<O> fetchPage(Pageable page,
                                     SelectLimitStep<?> selectLimitStep,
                                     Class<O> pojoType) {
        return fetchPage(page, selectLimitStep, r -> r.into(pojoType));
    }

    @Override
    public void updateSelective(P object) {
        this.updateSelective(Collections.singletonList(object));
    }

    @SuppressWarnings("unchecked")
    @Override
    public void updateSelective(P... objects) {
        this.updateSelective(Arrays.asList(objects));
    }

    @Override
    public void updateSelective(Collection<P> objects) {

        if (objects.size() > 1) {
            ctx().batchUpdate(recordsSelective(objects, true)).execute();
        } else if (objects.size() == 1) {
            recordsSelective(objects, true).get(0).update();
        }
    }

    @Override
    public void insertSelective(P object) {
        R record = recordsSelective(Collections.singletonList(object), false).get(0);
        record.insert();
        record.into(object);
    }

    /**
     * 重写 DAOImpl.insert 方法是因为
     * 在默认配置下父级的方法不会进行批量插入操作，而是遍历每个对象
     * 进行Insert操作，会产生N条SQL语句，影响性能
     */
    @Override
    public void insert(Collection<P> objects) {
        Settings settings = configuration().settings();
        Boolean oldConfigIsReturnRecord = settings.isReturnRecordToPojo();
        if (objects.size() > 1 && !settings.isReturnAllOnUpdatableRecord()) {
            settings.setReturnRecordToPojo(false);
        }
        try {
            super.insert(objects);
        } finally {
            settings.setReturnRecordToPojo(oldConfigIsReturnRecord);
        }
    }

    @SuppressWarnings("unchecked")
    @Override
    public void insertSelective(P... objects) {
        insertSelective(Arrays.asList(objects));
    }

    @Override
    public void insertSelective(Collection<P> objects) {
        if (objects.size() > 0) {
            ctx().batchInsert(recordsSelective(objects, false)).execute();
        }
    }

    private List<R> recordsSelective(Collection<P> objects,
                                     boolean forUpdate) {
        var result = new ArrayList<R>(objects.size());
        var primaryKeyFieldList = getPrimaryKeyFieldList();
        DSLContext ctx = ctx();

        for (P object : objects) {
            R record = ctx.newRecord(getTable(), object);
            if (forUpdate && primaryKeyFieldList != null) {

                for (Field<?> field : primaryKeyFieldList) {
                    record.changed(field, false);
                }
            }

            for (int i = 0; i < record.size(); i++) {
                Object data = record.get(i);
                record.changed(i, data != null);
            }
            result.add(record);
        }

        return result;
    }

    private List<TableField<R, ?>> getPrimaryKeyFieldList() {
        var primaryKey = getTable().getPrimaryKey();
        boolean primaryKeyExists = Optional.ofNullable(primaryKey).isPresent();
        return primaryKeyExists ? primaryKey.getFields() : ListUtil.empty();
    }

    /**
     * 不允许重写
     *
     */
    protected List<SortField<?>> getOrderBySortFields() {
        List<SortField<?>> sortFields = CollUtil.newArrayList();
        var pageable = PageUtils.getPageable();
        var sort = pageable.getSort();
        if (sort.isUnsorted())
            return sortFields;

        if (sort.isSorted()) {
            Sort.Order order = sort.stream().findFirst().orElseThrow();
            var toSortProperty = order.getProperty();
            var toSortTable = getToSortTable(toSortProperty);

            var sortedField = field(
                    name(
                            toSortTable.getName(),
                            StrUtil.toUnderlineCase(toSortProperty)
                    )
            );
            // 字段不存在
            if (toSortTable.indexOf(sortedField) == -1)
                return CollUtil.newArrayList();

            Sort.Direction direction = order.getDirection();
            boolean isDescSort = direction.equals(Sort.Direction.DESC);
            SortOrder sortOrder = isDescSort ? SortOrder.DESC : SortOrder.ASC;

            boolean nullable = sortedField.getDataType().nullable();
            if (nullable) {
                Condition isNullCon = sortedField.isNull();
                if (sortOrder.equals(SortOrder.DESC))
                    sortFields.add(isNullCon.desc());
                else
                    sortFields.add(isNullCon.asc());
                sortFields.add(sortedField.sort(sortOrder));
                return sortFields;
            }
            sortFields = Collections.singletonList(
                    sortedField.sort(sortOrder)
            );
        }
        return sortFields;
    }

    /**
     * 获取排序的表信息,默认是当前DAO泛型类型的表
     * <p>
     * 允许重写
     * 特殊情况,子类处理
     */
    public TableImpl<?> getToSortTable(String toSortProperty) {
        return (TableImpl<?>) getTable();
    }


    static Select<?> paginate(
            DSLContext ctx,
            Select<?> original,
            Field<?>[] sort,
            int limit,
            int offset
    ) {
        Table<?> u = original.asTable("u");
        Field<Integer> totalRows = DSL.count().over().as("total_rows");
        Field<Integer> row = rowNumber().over().orderBy(u.fields(sort))
                .as("row");

        Table<?> t = ctx
                .select(u.asterisk())
                .select(totalRows, row)
                .from(u)
                .orderBy(u.fields(sort))
                .limit(limit)
                .offset(offset)
                .asTable("t");

        Select<?> result = ctx
                .select(t.fields(original.getSelect().toArray(Field[]::new)))
                .select(
                        DSL.count().over().as("actual_page_size"),
                        field(max(t.field(row)).over().eq(t.field(totalRows)))
                                .as("last_page"),
                        t.field(totalRows),
                        t.field(row),
                        t.field(row).minus(inline(1)).div(limit).plus(inline(1))
                                .as("current_page"))
                .from(t)
                .orderBy(t.fields(sort));

        // System.out.println(result);
        return result;
    }


    @Override
    public <O> PageInfo<O> fetchPage(Select<?> originalSelect,
                                     TableImpl<?> table,
                                     RecordMapper<? super Record, O> mapper) {


        Pageable pageable = PageUtils.buildPageRequest();
        Sort sort = pageable.getSort();
        if (sort.isSorted()) {
            Sort.Order order = sort.stream().findFirst().orElseThrow();
            String property = order.getProperty();
            Sort.Direction direction = order.getDirection();
//            Field<?> sortedFields =
//
//                    field(name(table.getName(),
//                                    StrUtil.toUnderlineCase(property)
//                            )
//                    ).sort(direction.equals(Sort.Direction.DESC) ?
//                            SortOrder.DESC :
//                            SortOrder.ASC);


        }

        return null;
    }


}
